IF EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'usp_UPDMS_CRD_ITEM_BIZ_Insert_Xml')
	BEGIN
		DROP Procedure usp_UPDMS_CRD_ITEM_BIZ_Insert_Xml
	END
GO

SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
GO
/******************************************************************************
**	Name : usp_UPDMS_CRD_ITEM_BIZ_Insert_xml
**	Desc : 카드사용내역을 한꺼번에 Insert한다.
**	Test Exec Query : Exec usp_UPDMS_CRD_ITEM_BIZ_Insert_Xml
**	Called by : Crd_Dac_UPDMS_CRD_ITEM_BIZ.cs
**	Program ID : Crd4010b
**	Auth : 송시명
**	Date : 2012-04-04
*******************************************************************************
**	Change History
*******************************************************************************
**	Date:		Author:		Description:
**	--------	--------	---------------------------------------
**	
*******************************************************************************/
CREATE PROC [dbo].[usp_UPDMS_CRD_ITEM_BIZ_Insert_Xml]
@ls_xml nvarchar(max) = '<UPDMS></UPDMS>' --루트노드
AS

BEGIN

DECLARE @iXmlHandle int
EXEC sp_xml_preparedocument @iXmlHandle OUTPUT, @ls_xml

INSERT INTO UPDMS_CRD_ITEM_BIZ (	
       Card_Cd,
       Check_Yn,
       Biz_Div,
       Account,
       Contents,
       Amount,
       Biz_Dt,
       Paymnt_Dt,
       Confirm_Yn,
       Remark,
       Latitude,
       Longitude,
       Create_User,
       Create_Dt,
       Update_User,
       Update_Dt
)
SELECT Card_Cd,
       'N',
       Biz_Div,
       CASE Account WHEN '-' THEN 'AC997' ELSE Account END, -- 값이 없으면 기타(통계미반영)으로 저장
       Contents,
       Amount,
       Biz_Dt,
       Paymnt_Dt,
       'N',
       Remark,
       '',
       '',
       Create_User,
       GETDATE(),
       Update_User,
       GETDATE()
  FROM OPENXML(@iXmlHandle, '/UPDMS/UPDMS_CRD_ITEM_BIZ', 2) 
  WITH (
       Card_Cd nvarchar(5),
       Biz_Div nvarchar(5),
       Account nvarchar(5),
       Contents nvarchar(100),
       Amount int,
       Biz_Dt nvarchar(10),
       Paymnt_Dt nvarchar(10),
       Remark nvarchar(2000),
       Create_User nvarchar(20),
       Update_User nvarchar(20)
       ) X
 WHERE X.Card_Cd IS NOT NULL
   AND X.Biz_Div IS NOT NULL
   AND X.Contents IS NOT NULL
   AND X.Amount IS NOT NULL
   AND X.Biz_Dt IS NOT NULL
   AND X.Paymnt_Dt IS NOT NULL

--메모리 해제
EXEC sp_xml_removedocument @iXmlHandle

END